
package pos.storage.db.sql;

/**
 * This class is the repository for the SQLs used in the product
 * @author srikanth ramu
 */
public class SQL {
    public final static String SELECTITEM = "SELECT * FROM ITEMS";
    public final static String SelectItemWithCritera = "SELECT "
            + "I.ITEMID, ITEMNAME, ITEMBASEPRICE as Price, "
            + "(PromotionItemPriceAdjustment) * 100 AS Discount, 0 as Tax,"
            + "IF((PromotionItemPriceAdjustment) > 0, "
            + "ITEMBASEPRICE - (PromotionItemPriceAdjustment) * ITEMBASEPRICE, ITEMBASEPRICE) as Total, 0 as PItemId "
            + "FROM ITEMS I LEFT JOIN PROMOTIONITEMS PI ON I.ITEMID = PI.ITEMID WHERE UPC = (?) "
            + "GROUP BY ITEMID LIMIT 1";
    public final static String INSERTITEM = "INSERT INTO ITEMS(ITEMID,ITEMNAME, ITEMDESCRIPTION,UPC, ITEMBASEPRICE, DATETIMEOFMANUFACTURE, EXPIRYDATETIME, DATETIMEMODIFIED, DATETIMECREATED)" +
                                              "VALUES(?,?,?,?,?,?,?,?,?)";
    public final static String AuthGetPassword = "SELECT EmployeeId, EmployeeUserName, EmployeeFirstName, EmployeeDesignation, EmployeeAuthorizationLevel, EmployeePassword FROM employees";
    
    public final static String INSERTNEWCUSTOMER = "INSERT INTO CUSTOMERS(CUSTOMERFIRSTNAME, CUSTOMERLASTNAME, CUSTOMERPHONENUMBER, CUSTOMERUNITNUMBER, CUSTOMERADDRESS,"
            + "CUSTOMERPOSTALCODE,CUSTOMERCITY,CUSTOMERCOUNTRY,CUSTOMEREMAIL,CUSTOMERPASSWORD,STORECARDNUMBER,STORECARDISSUEDATETIME,STORECARDEXPIRYDATETIME,"
            + "DATETIMECREATED,DATETIMEMODIFIED)" +"VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

    //Insert queries for PROMOTIONS and PROMOTION ITEMS
    public final static String INSERTPROMOTION = "INSERT INTO Promotions " +
                                                 "(PromotionId, PromotionName, PromotionDescription, PromotionConditions, " +
                                                 "PromotionType, PromotionPriceAdjustment,  " +
                                                 "PromotionCreatedByEmployeeId, " +
                                                 "PromotionStartDateTime, PromotionEndDateTime, " +
                                                 "DateTimeCreated, DateTimeModified)" +
                                                 "VALUES(?,?,?,?,?,?,?,?,?,?,?)";

    public final static String INSERTPROMOTIONITEMS = "INSERT INTO PromotionItems "
                                                    + "(PromotionItemName, "
                                                    + "PromotionItemDescription, PromotionPriceAdjustment, "
                                                    + "PromotionItemConditions, PromotionItemPriceAdjustment, "
                                                    + "DateTimeCreated, DateTimeModified, ItemId, PromotionId) "
                                                    + "SELECT ?, ?, ?, ?, ?, NOW(), NOW(), ItemId, ? "
                                                    + "FROM Items ";

    //INSERT queries for price adjustments table
    public final static String INSERTPRICEADJUSTMENT = ""
            + "INSERT INTO PriceAdjustments "
            + "(PriceAdjustmentId, PriceAdjustmentAmount, PriceAdjustedByEmployeeId, "
            + " PriceAdjustmentType, PriceAdjustmentDescription, PurchasedItemId, "
            + " PromotionId, DateTimeCreated, DateTimeModified) "
            + "VALUES "
            + "(?,?,?,?,?,?,?, NOW(), NOW()) ";

    public final static String SelectCustomerWithPhoneNum = "SELECT CUSTOMERFIRSTNAME, CUSTOMERLASTNAME,CUSTOMERID FROM CUSTOMERS WHERE CUSTOMERPHONENUMBER = ? ";
    public final static String SelectCustomerWithStoreCardNUm = "SELECT CUSTOMERFIRSTNAME, CUSTOMERLASTNAME,CUSTOMERID FROM CUSTOMERS WHERE STORECARDNUMBER = ?";

    //Following SQLs inserted for the Add Delivery Info
    public final static String INSERT_DELIVERY_MANIFEST = "INSERT INTO deliverymanifest " +
                                                          "(OrderId, PurchasedItemId, DeliveryInformationId, DateTimeCreated, DateTimeModified" +
                                                          "VALUES(?,?,?,?,?)";
    public final static String GET_SHIPPING_CHARGE = "SELECT * FROM shippingcosts where PostalCode = ?";
    public final static String INSERT_DELIVERY_INFO = "INSERT INTO deliveryinformation " +
                                                        "(DeliveryInformationId, DeliveryUnitNumber, DeliveryAddress, DeliveryPostalCode,"+
                                                        "DeliveryCity, DeliveryCountry, DeliveryPhoneNumber, ShippingCost,"+
                                                        "DateTimeCreated, DateTimeModified) " +
                                                        "VALUES(?,?,?,?,?,?,?,?,?,?)";
    
    public final static String INSERTORDER = "INSERT INTO ORDERS " +
                                                 "(OrderId, OrderDateTime, StoreId, " +
                                                 "OrderTransactionSource, OrderTotalBasePrice,  " +
                                                 "OrderTotalAdjustedAmount, " +
                                                 "OrderTotalPrice, OrderTakenByEmployeeId," +
                                                 "DateTimeCreated, DateTimeModified, CustomerId)" +
                                                 "VALUES(?,?,?,?,?,?,?,?,?,?,?)";
    
            public final static String INSERTORDERWOCUST = "INSERT INTO ORDERS " +
                                                 "(OrderId, OrderDateTime, StoreId, " +
                                                 "OrderTransactionSource, OrderTotalBasePrice,  " +
                                                 "OrderTotalAdjustedAmount, " +
                                                 "OrderTotalPrice, OrderTakenByEmployeeId, " +
                                                 "DateTimeCreated, DateTimeModified)" +
                                                 "VALUES(?,?,?,?,?,?,?,?,?,?)";
    public final static String INSERTPURCHASEDITEMS = "INSERT INTO PurchasedItems "
                                                    + "(PurchasedItemId, "
                                                    + "ItemId, OrderId, "
                                                    + "PurchasedItemPriceAdjustedAmount, PurchasedItemBasePrice, "
                                                    + "PurchasedItemFinalPrice, IsToBePickedUpInStore, DateTimeCreated, DateTimeModified) "
                                                    + "VALUES(?,?,?,?,?,?,?,?,?)";

    //Following SQLs inserted for the Applet Client
    public final static String GET_ITEMS = "SELECT * FROM items WHERE ItemId = ?";
    public final static String GET_ITEM_INFO = "SELECT * FROM categoryitems WHERE categoryId = ?";
    public final static String SELECT_ITEM_DETAILS = "SELECT "
            + "I.ItemId, I.ItemName, I.UPC, I.ItemDescription, ItemBasePrice as Price, "
            + "(PromotionItemPriceAdjustment) * 100 AS Discount, "
            + "IF((PromotionItemPriceAdjustment) > 0, "
            + "ITEMBASEPRICE - (PromotionItemPriceAdjustment) * ITEMBASEPRICE, ITEMBASEPRICE) as Total, 0 as Tax, 0 as PItemId "
            + "FROM ITEMS I LEFT JOIN PROMOTIONITEMS PI ON I.ITEMID = PI.ITEMID WHERE I.ItemId = ? "
            + "GROUP BY ITEMID";
    public final static String VALIDATE_CUSTOMER = "SELECT * FROM Customers WHERE CustomerId = ? AND CustomerPassword = ?";

    //REPORT QUERIES
    //Top 10 most purchased items (by quantity)
    public final static String REPORT_TOP10_BY_QUANTITY = ""
            + "SELECT i.ItemName AS itemItemName, "
            + "       count(*) as numPurchases "
            + "FROM PurchasedItems pi LEFT JOIN Items i ON pi.ItemId = i.ItemId "
            + "GROUP BY i.itemId "
            + "ORDER BY numPurchases DESC "
            + "LIMIT 10";

    public final static String numREPORT_TOP10_BY_QUANTITY = ""
            + "SELECT count(*) AS numRecords "
            + "FROM ("
            + REPORT_TOP10_BY_QUANTITY
            + ") AS tempForRecordCount";
    
    //Top 10 most purchased items (by value)
    public final static String REPORT_TOP10_BY_VALUE = ""
            + "SELECT i.ItemName AS itemItemName, "
            + "       sum(pi.purchasedItemFinalPrice) as valuePurchases "
            + "FROM PurchasedItems pi LEFT JOIN Items i ON pi.ItemId = i.ItemId "
            + "GROUP BY i.itemId "
            + "ORDER BY valuePurchases DESC "
            + "LIMIT 10";

    public final static String numREPORT_TOP10_BY_VALUE = ""
            + "SELECT count(*) AS numRecords "
            + "FROM ("
            + REPORT_TOP10_BY_VALUE
            + ") AS tempForRecordCount";

    //Category breakdown of purchases (top 10 categories)
    public final static String REPORT_TOP10_CATEGORIES = ""
            + "SELECT c.categoryName AS categoryCategoryName, "
            + "       sum(PurchasedItemFinalPrice) AS sumCategory, "
            + "       ci.categoryId AS catItemsCategoryId "
            + "FROM (CategoryItems ci LEFT JOIN PurchasedItems pi ON ci.itemId = pi.itemId ) "
            + "      INNER JOIN Category c ON ci.CategoryId = c.CategoryId "
            + "GROUP BY ci.CategoryId "
            + "ORDER BY sumCategory DESC "
            + "LIMIT 10";

    public final static String numREPORT_TOP10_CATEGORIES = ""
            + "SELECT count(*) AS numRecords "
            + "FROM ("
            + REPORT_TOP10_CATEGORIES
            + ") AS tempForRecordCount";

    //Total value of purchases by month
    public final static String TOTAL_VALUE_PURCHASE_BY_MONTH = ""
            + "SELECT sum(PurchasedItemFinalPrice) as sumPurchases, "
            + "       month(pi.DateTimeModified) as monthNo "
            + "FROM purchasedItems pi LEFT JOIN items i ON pi.itemId = i.itemId "
            + "WHERE MONTH(pi.dateTimeModified) <= MONTH(CURDATE()) AND YEAR(pi.DateTimeModified) = YEAR(CURDATE()) "
            + "ORDER BY MONTH(pi.DateTimeModified) DESC "
            + "LIMIT 3 ";

    public final static String numTOTAL_VALUE_PURCHASE_BY_MONTH = ""
            + "SELECT count(*) AS numRecords "
            + "FROM ("
            + TOTAL_VALUE_PURCHASE_BY_MONTH
            + ") AS tempForRecordCount";

    //Total value of purchases by week
    public final static String TOTAL_VALUE_PURCHASE_BY_WEEK = ""
            + "SELECT sum(PurchasedItemFinalPrice) as sumPurchases, "
            + "       week(pi.dateTimeModified) as weekNo "
            + "FROM purchasedItems pi LEFT JOIN items i ON pi.itemId = i.itemId "
            + "WHERE WEEK(pi.DateTimeModified) <= WEEK(CURDATE()) AND YEAR(pi.DateTimeModified) = YEAR(CURDATE()) "
            + "LIMIT 4 ";

    public final static String numTOTAL_VALUE_PURCHASE_BY_WEEK = ""
            + "SELECT count(*) AS numRecords "
            + "FROM ("
            + TOTAL_VALUE_PURCHASE_BY_WEEK
            + ") AS tempForRecordCount";
    
    //For Search Use Case:
    public final static String SEARCH_BY_NAME = "SELECT I.UPC, I.ItemName, I.ItemDescription, G.CategoryName from"+
            " Items I, CategoryItems C, Category G where I.ItemName like ?"+
            " AND I.ItemId = C.ItemId AND G.CategoryId = C.CategoryId";
    
    public final static String SEARCH_BY_CATEGORY = "SELECT I.UPC, I.ItemName, I.ItemDescription, G.CategoryName from"+
            " Items I, CategoryItems C, Category G where G.CategoryName like ?"+
            " AND I.ItemId = C.ItemId AND G.CategoryId = C.CategoryId";

    public final static String SEARCH = "SELECT I.UPC, I.ItemName, I.ItemDescription, G.CategoryName from"+
            " Items I, CategoryItems C, Category G where G.CategoryName like ? AND I.ItemName like (?)"+
            " AND I.ItemId = C.ItemId AND G.CategoryId = C.CategoryId";

    public final static String INSERT_PAYMENTDETAILS = "INSERT INTO PAYMENTDETAILS(PAYMENTDETAILSID,CUSTOMERID, ORDERID, PAYMENTTYPE, PAYMENTCARDNUMBER)"
            +"VALUES(?,?,?,?,?)";
    public final static String INSERT_STOREITEMINVENTORY = "INSERT INTO STOREITEMINVENTORY (ITEMQUANTITYAVAILABLEDID,ITEMID,STOREID,DATETIMECREATED,DATETIMEMODIFIED) "
            + "VALUES (?,?,?,?,?)";
    public final static String BUILD_TABLE = "SELECT UPC, ItemName, ItemBasePrice, ItemQuantityAvailableId, DateTimeOfManufacture, ExpiryDateTime "
                + "FROM items, storeiteminventory "
                + "WHERE items.ItemId = storeiteminventory.ItemId";

    public final static String DISPALY_TABLE = "SELECT * FROM ITEMS";
    public final static String DELETEITEM = "DELETE FROM ITEMS WHERE ITEMID = ?";
    public final static String INSERT_CATEGORYITEM = "INSERT INTO CATEGORYITEMS (CategoryItemId,CategoryId,ItemId,DateTimeCreated,DateTimeModified)"
            + "VALUES (?,?,?,?,?)";
}
